﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Common;
using System.Data;

namespace Garlic.DAL
{
    public partial class StSchedulesDAL
    {
        public const string ST_SCHEDULES_SELECT_FOR_TABLE =
@"select distinct b.vsl_enname,
       b.vsl_cnname,
       b.vsl_vtpcode,
       a.scd_ivoyage,
       a.scd_evoyage,
       a.scd_eta,
       a.scd_etd,
       c.cst_cstm,
       a.scd_fixscdshpfg,
       a.scd_culoc,
       Q.rv_meaning as SCD_STATUS,
       a.scd_pilotage,
        d.pot_portcd || '_' || d.pot_portlocnnm as pot_fname ,
       e.pot_portcd || '_' || e.pot_portlocnnm as pot_nname,
       a.scd_id,
        F.RV_MEANING,
        WEB_f_getsln(a.scd_id) as SERVICELINE
from st_schedules a inner join
     st_vessels b on b.vsl_id = a.scd_vsl_id left join
     st_customers c on c.cst_id = a.scd_ter_cst_id left join     
     st_ports d on d.pot_id = a.scd_pot_fid left join
     st_ports e on e.pot_id = a.scd_pot_nid 
     left join st_voyages V ON a.scd_id= V.Voy_Scd_Id
     left join st_service_lines  SL
     on V.Voy_Sln_Id=sl.sln_id
     left join ST_REF_CODES F on F.RV_LOW_VALUE = A.Scd_Culoc and F.RV_DOMAIN = '抵港位置' 
     left join ST_REF_CODES Q on Q.RV_LOW_VALUE = A.SCD_STATUS and Q.RV_DOMAIN = 'SCHEDULE_STATUS' 
    {0} ";

        private const string deleteStr = @"delete from  st_schedules where scd_id= {0}";

        /// <summary>
        /// GetTable
        /// </summary>
        /// <param name="textWhere">conditon</param>
        /// <param name="orderText">sort</param>
        /// <returns></returns>
        public DataTable GetTable(string textWhere, string orderText)
        {
            string query = string.Empty;
            if (!string.IsNullOrEmpty(textWhere))
                query += " where " + textWhere;
            if (!string.IsNullOrEmpty(orderText))
                query += " order by " + orderText;

            return GetDataTable(string.Format(ST_SCHEDULES_SELECT_FOR_TABLE, query));
        }


        /// <summary>
        /// Get DataTable
        /// </summary>
        /// <param name="sql">SQL statement</param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(sql);
            DataTable dt = db.ExecuteDataTable(dbCommand);
            if (dt != null)
            {
                dt.TableName = "ST_SCHEDULES";
            }
            return dt;
        }

        /// <summary>
        /// 写数据到船期表
        /// </summary>
        /// <returns></returns>
        public string InsertToTable(string user_id, string cnname, string enname, string ivoyage, string evoyage, DateTime eta
            , DateTime etd, string state, string ter_id, string culoc, string pilotage, string fid, string nid, string sln_id, string flt_id,string vsl_code)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("WEB_SP_INSERT_SCHEDULES");
            db.AddInParameter(dbCommand, "P_USER_ID", DbType.Int32, user_id);
            db.AddInParameter(dbCommand, "P_VESSEL_CNAME", DbType.String, cnname);
            db.AddInParameter(dbCommand, "P_VESSEL_ENAME", DbType.String, "OOCL LONG BEACH");
            db.AddInParameter(dbCommand, "P_IVOYAGE", DbType.String, ivoyage);
            db.AddInParameter(dbCommand, "P_EVOYAGE", DbType.String, evoyage);
            db.AddInParameter(dbCommand, "P_VSL_ID", DbType.Int32, int.Parse(vsl_code));
            db.AddInParameter(dbCommand, "P_ETA", DbType.DateTime, eta);
            db.AddInParameter(dbCommand, "P_ETD", DbType.DateTime, etd);
            db.AddInParameter(dbCommand, "P_STATE", DbType.Int32, state);
            db.AddInParameter(dbCommand, "P_TER_ID", DbType.Int32, ter_id);

            db.AddInParameter(dbCommand, "P_CULOC", DbType.String, culoc);
            db.AddInParameter(dbCommand, "P_PILOTAGE", DbType.String, pilotage);
            db.AddInParameter(dbCommand, "P_POT_FID", DbType.Int32, fid);
            db.AddInParameter(dbCommand, "P_POT_NID", DbType.Int32, nid);
            db.AddInParameter(dbCommand, "P_SLN_ID", DbType.Int32, sln_id);
            db.AddInParameter(dbCommand, "P_FLT_ID", DbType.Int32, flt_id);
            db.AddOutParameter(dbCommand, "results", DbType.String, 200);
            dbCommand.Parameters["results"].Direction = ParameterDirection.Output;

            db.ExecuteNonQuery(dbCommand);
            return dbCommand.Parameters["results"].Value.ToString();
        }

        /// <summary>
        /// 删除船期数据.
        /// </summary>
        /// <param name="scd_id"></param>
        public void DeleteBySCD_ID(string scd_id)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(string.Format(deleteStr, scd_id));
            db.ExecuteNonQuery(dbCommand);
        }

        /// <summary>
        /// 根据船期ID找到该记录。
        /// </summary>
        /// <param name="scd_id"></param>
        /// <returns></returns>
        public DataTable SelectTableBySCD_ID(string scd_id)
        {
            string query = "select * from st_schedules where scd_id=" + scd_id + "";
            return GetDataTable(query);
        }

        /// <summary>
        ///  根据SLN_ID查询服务航线信息。
        /// </summary>
        /// <param name="line_ID"></param>
        /// <returns></returns>
        public DataTable SelectService_Line(string line_ID)
        {
            string query = "select * from st_service_lines where sln_id=" + line_ID;
            return GetDataTable(query);
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="scd_id"></param>
        /// <param name="user_id"></param>
        /// <param name="cnname"></param>
        /// <param name="enname"></param>
        /// <param name="ivoyage"></param>
        /// <param name="evoyage"></param>
        /// <param name="eta"></param>
        /// <param name="etd"></param>
        /// <param name="state"></param>
        /// <param name="ter_id"></param>
        /// <param name="culoc"></param>
        /// <param name="pilotage"></param>
        /// <param name="fid"></param>
        /// <param name="nid"></param>
        /// <param name="flt_id"></param>
        /// <returns></returns>
        public string UpdateSchedule(string scd_id, string user_id, string cnname, string enname, string ivoyage, string evoyage, DateTime eta
            , DateTime etd, string state, string ter_id, string culoc, string pilotage, string fid, string nid, string flt_id,string vsl_code)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("web_sp_update_schedules");
            db.AddInParameter(dbCommand, "P_SCD_ID", DbType.Int32, scd_id);
            db.AddInParameter(dbCommand, "P_USER_ID", DbType.Int32, user_id);
            db.AddInParameter(dbCommand, "P_VESSEL_CNAME", DbType.String, cnname);
            db.AddInParameter(dbCommand, "P_VESSEL_ENAME", DbType.String, enname);
            db.AddInParameter(dbCommand, "P_VSL_ID", DbType.Int32, int.Parse(vsl_code));
            db.AddInParameter(dbCommand, "P_IVOYAGE", DbType.String, ivoyage);
            db.AddInParameter(dbCommand, "P_EVOYAGE", DbType.String, evoyage);
            db.AddInParameter(dbCommand, "P_ETA", DbType.DateTime, eta);
            db.AddInParameter(dbCommand, "P_ETD", DbType.DateTime, etd);
            db.AddInParameter(dbCommand, "P_STATE", DbType.Int32, state);
            db.AddInParameter(dbCommand, "P_TER_ID", DbType.Int32, ter_id);

            db.AddInParameter(dbCommand, "P_CULOC", DbType.String, culoc);
            db.AddInParameter(dbCommand, "P_PILOTAGE", DbType.String, pilotage);
            db.AddInParameter(dbCommand, "P_POT_FID", DbType.Int32, fid);
            db.AddInParameter(dbCommand, "P_POT_NID", DbType.Int32, nid);
            db.AddInParameter(dbCommand, "P_FLT_ID", DbType.Int32, int.Parse(flt_id));
            db.AddOutParameter(dbCommand, "results", DbType.String, 200);
            dbCommand.Parameters["results"].Direction = ParameterDirection.Output;

            db.ExecuteNonQuery(dbCommand);
            return dbCommand.Parameters["results"].Value.ToString();
        }

    }//end class
}
